﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Reports.RoznDocIncome
{
    public class DataLoader
    {
        public static void GetData(Reports.RoznDocIncome.DataSet.DataDataTable data, string ConnectionString,DateTime dateF,DateTime dateT)
        {
            string QueryString =
@"With Data(SupplierName,DocDate,DocNum,SupplierDocNum,SupplierDocDate,SumNOTAX,RetailSum,NDSTax) as
(
SELECT     CA.Contragents.Name, CA.Documents.DocDate, CA.Documents.DocNumber, 
CA.Documents.ContragentDocNumber, CA.Documents.ContragentDocDate, 
sum(vwInputLines.SupplierPrice * vwInputLines.DocQntty * (cast(100 as decimal)/(100+isnull(vwInputLines.NDSTax,0)))),
-- sum(vwInputLines.SupplierSumNOTAX),
sum(vwInputLines.RetailSum), isnull(vwInputLines.NDSTax,0)
FROM  CA.Documents INNER JOIN
CA.Contragents ON CA.Documents.ContragentId = CA.Contragents.Id INNER JOIN
CD.Parties ON CA.Documents.Id = CD.Parties.DocId INNER JOIN
vwInputLines ON CD.Parties.Id = vwInputLines.PartyId
WHERE     (CA.Documents.DocTypeId = 2) AND CA.Documents.DocDate >= @DateF AND CA.Documents.DocDate < @DateT
group by CA.Contragents.Name, CA.Documents.DocDate, CA.Documents.DocNumber, 
CA.Documents.ContragentDocNumber, CA.Documents.ContragentDocDate,vwInputLines.NDSTax

)
select SupplierName,DocDate,DocNum,SupplierDocNum,SupplierDocDate,
SumNOTAX as [Sum],NDSTax as VatPercent, RetailSum as AptSum, 
(RetailSum * NDSTax/(100+NDSTax)) as AptVatSum
--,(RetailSum - (RetailSum * NDSTax/(100+NDSTax)) - SumNOTAX) as nzn
from Data
--where SumNOTAX > 0
order by 2";
            try
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    SqlCommand command = new SqlCommand(QueryString, connection);
                    SqlParameter DateF = new SqlParameter("@DateF", SqlDbType.DateTime);
                    DateF.Value = dateF;
                    command.Parameters.Add(DateF);
                    SqlParameter DateT = new SqlParameter("@DateT", SqlDbType.DateTime);
                    DateT.Value = dateT;
                    command.Parameters.Add(DateT);
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    adapter.Fill(data);
                }
            }
            catch (SqlException se)
            {
                System.Windows.Forms.MessageBox.Show(se.Message);
            }
            catch (Exception ee)
            {
                System.Windows.Forms.MessageBox.Show(ee.Message);
            }
        }
    }
}
